﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VVERSART
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                default:
                case "YGR":
                case "TST":
                    #region=========
                    dwReturn["VMANDANT"] = app.Mandant;
                    dwReturn["VVERSART"] = "W  ";
                    dwReturn["VSPED"] = "1  ";
                    dwReturn["VBEZ"] = "                    ";
                    dwReturn["VVSKLAS"] = "S";
                    dwReturn["VLIEFNR"] = "00000000";
                    dwReturn["VEBUCHNR"] = "00";
                    dwReturn["VTPMLKZ"] = "   ";
                    dwReturn["VVEZWEIG"] = " ";
                    dwReturn["VAZOLLST"] = "      ";
                    dwReturn["VDISP"] = "                    ";
                    dwReturn["VTEL"] = "                    ";
                    dwReturn["VFRBRIEF"] = "        ";
                    dwReturn["VFRBUGRP"] = "  ";
                    dwReturn["VFRBPARAM"] = "     ";
                    dwReturn["VFRBANZ"] = "0";
                    dwReturn["VFRBTXT"] = "   ";
                    dwReturn["VLADELST"] = "        ";
                    dwReturn["VLDLUGRP"] = "  ";
                    dwReturn["VLDLPARAM"] = "     ";
                    dwReturn["VLDLANZ"] = "0";
                    dwReturn["VLDLTXT"] = "   ";
                    dwReturn["VETIKETT"] = "        ";
                    dwReturn["VFORMETI"] = "        ";
                    dwReturn["VETIANZ"] = "0";
                    dwReturn["VETIDRKZ"] = " ";
                    dwReturn["VETITXT"] = "   ";
                    dwReturn["VPRAEFIX"] = "                    ";
                    dwReturn["VLFDNRANZ"] = "0";
                    dwReturn["VPRUEFVERF"] = " ";
                    dwReturn["VPRFELDLG1"] = "0";
                    dwReturn["VPRFELDKZ1"] = " ";
                    dwReturn["VPRFELDLG2"] = "0";
                    dwReturn["VPRFELDKZ2"] = " ";
                    dwReturn["VPRFELDLG3"] = "0";
                    dwReturn["VPRFELDKZ3"] = " ";
                    dwReturn["VPRFELDLG4"] = "0";
                    dwReturn["VPRFELDKZ4"] = " ";
                    dwReturn["VPRFELDLG5"] = "0";
                    dwReturn["VPRFELDKZ5"] = " ";
                    dwReturn["VLO"] = "  ";
                    dwReturn["VLB"] = "  ";
                    dwReturn["VPLATZ"] = "        ";
                    dwReturn["VTTAG1"] = " ";
                    dwReturn["VTTAG2"] = " ";
                    dwReturn["VTTAG3"] = " ";
                    dwReturn["VTTAG4"] = " ";
                    dwReturn["VTTAG5"] = " ";
                    dwReturn["VTTAG6"] = " ";
                    dwReturn["VTTAG7"] = " ";
                    dwReturn["VSTKGKZ"] = " ";
                    dwReturn["VVERSD"] = "0";
                    dwReturn["VAVISBILD"] = "J";
                    dwReturn["VVSFREMDF"] = "   ";
                    dwReturn["VVSFREMDUF"] = "   ";
                    dwReturn["VDRUCKLS"] = " ";
                    dwReturn["VFRANKAT"] = " ";
                    dwReturn["VPACKTYP"] = " ";
                    dwReturn["VDEPOT"] = "     ";
                    dwReturn["VSERVICE"] = "   ";
                    dwReturn["VBOOKNO"] = "0";
                    dwReturn["VBOOKPR"] = "                    ";
                    dwReturn["VBOOKANZ"] = "0";
                    dwReturn["VBOOKVERF"] = " ";
                    dwReturn["VBOOKEB"] = " ";
                    dwReturn["VSUFFIX"] = "          ";
                    dwReturn["VBOOKSUFF"] = "          ";
                    dwReturn["VPPZVON"] = "00";
                    dwReturn["VPPZBIS"] = "00";
                    dwReturn["VBPZVON"] = "00";
                    dwReturn["VBPZBIS"] = "00";
                    dwReturn["VRVERSART"] = "   ";
                    dwReturn["VRSPED"] = "   ";
                    dwReturn["VROUTING"] = " ";
                    dwReturn["VURL"] = "                                                                                                                                                                               ";
                    dwReturn["VFILLER"] = " 000                                                       ";
                    dwReturn["VAENZL"] = "2";
                    dwReturn["VAENB"] = "        ";
                    dwReturn["VAEND"] = "0";
                    dwReturn["VNEUB"] = "        ";
                    dwReturn["VNEUD"] = "0";

                    #endregion
                    break;
            }
        }
        public void InsertVERSART(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            dwData["VNEUB"] = app.User;
            dwData["VNEUD"] = date;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                default:
                case "TST":
                case "YGR":
                    or1.CommandText = "INSERT INTO VERSART ( VMANDANT,VVERSART,VSPED,VBEZ,VVSKLAS,VLIEFNR,VEBUCHNR,VTPMLKZ,VVEZWEIG,VAZOLLST,VDISP,VTEL,VFRBRIEF,VFRBUGRP,VFRBPARAM,VFRBANZ,VFRBTXT,VLADELST,VLDLUGRP,VLDLPARAM,VLDLANZ,VLDLTXT,VETIKETT,VFORMETI,VETIANZ,VETIDRKZ,VETITXT,VPRAEFIX,VLFDNRANZ,VPRUEFVERF,VPRFELDLG1,VPRFELDKZ1,VPRFELDLG2,VPRFELDKZ2,VPRFELDLG3,VPRFELDKZ3,VPRFELDLG4,VPRFELDKZ4,VPRFELDLG5,VPRFELDKZ5,VLO,VLB,VPLATZ,VTTAG1,VTTAG2,VTTAG3,VTTAG4,VTTAG5,VTTAG6,VTTAG7,VSTKGKZ,VVERSD,VAVISBILD,VVSFREMDF,VVSFREMDUF,VDRUCKLS,VFRANKAT,VPACKTYP,VDEPOT,VSERVICE,VBOOKNO,VBOOKPR,VBOOKANZ,VBOOKVERF,VBOOKEB,VSUFFIX,VBOOKSUFF,VPPZVON,VPPZBIS,VBPZVON,VBPZBIS,VRVERSART,VRSPED,VROUTING,VURL,VFILLER,VAENZL,VAENB,VAEND,VNEUB,VNEUD)"
                    + " VALUES (:VMANDANT:,:VVERSART:,:VSPED:,:VBEZ:,:VVSKLAS:,:VLIEFNR:,:VEBUCHNR:,:VTPMLKZ:,:VVEZWEIG:,:VAZOLLST:,:VDISP:,:VTEL:,:VFRBRIEF:,:VFRBUGRP:,:VFRBPARAM:,:VFRBANZ:,:VFRBTXT:,:VLADELST:,:VLDLUGRP:,:VLDLPARAM:,:VLDLANZ:,:VLDLTXT:,:VETIKETT:,:VFORMETI:,:VETIANZ:,:VETIDRKZ:,:VETITXT:,:VPRAEFIX:,:VLFDNRANZ:,:VPRUEFVERF:,:VPRFELDLG1:,:VPRFELDKZ1:,:VPRFELDLG2:,:VPRFELDKZ2:,:VPRFELDLG3:,:VPRFELDKZ3:,:VPRFELDLG4:,:VPRFELDKZ4:,:VPRFELDLG5:,:VPRFELDKZ5:,:VLO:,:VLB:,:VPLATZ:,:VTTAG1:,:VTTAG2:,:VTTAG3:,:VTTAG4:,:VTTAG5:,:VTTAG6:,:VTTAG7:,:VSTKGKZ:,:VVERSD:,:VAVISBILD:,:VVSFREMDF:,:VVSFREMDUF:,:VDRUCKLS:,:VFRANKAT:,:VPACKTYP:,:VDEPOT:,:VSERVICE:,:VBOOKNO:,:VBOOKPR:,:VBOOKANZ:,:VBOOKVERF:,:VBOOKEB:,:VSUFFIX:,:VBOOKSUFF:,:VPPZVON:,:VPPZBIS:,:VBPZVON:,:VBPZBIS:,:VRVERSART:,:VRSPED:,:VROUTING:,:VURL:,:VFILLER:,:VAENZL:,:VAENB:,:VAEND:,:VNEUB:,:VNEUD:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                    //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }

            Connect2Proxy(or1);
        }
        public DataSet QueryVERSART(DataRow dwData, int option = 1)
        { 
             
            switch (option)
            {
                case 1:
                    SResult = QVER01(dwData);
                    break;
                case 2:
                    SResult = QVER02(dwData);
                    break;
                case 3:
                    SResult = QVER03(dwData);
                    break;
             
                case -1:
                    SResult = QVER_1(dwData); break;
                case -2:
                    SResult = QVER_2(dwData); 
                    break;
            }
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVER01(DataRow dwData)
        { 

            or1 = OracleDB.CreateCommand();  
            or1.CommandText = "SELECT* FROM VERSART WHERE VVERSART=:VVERSART: AND VSPED=:VSPED:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);  
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVER02(DataRow dwData)
        { 
            or1 = OracleDB.CreateCommand();  
            or1.CommandText = "SELECT DISTINCT S.* FROM VVLIEF V JOIN  VERSART  S ON VVERSART=VVVERSART AND VSPED=VVSPED JOIN VVTLIEF T ON V.VVMANDANT=VVTMANDANT AND VVLIEFSCH=VVLIEFSCH"
            + " WHERE VVTMANDANT=:VVTMANDANT: AND VVTAUFKRS = :VVTAUFKRS: AND VVTAUFLFD=:VVTAUFLFD:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVER03(DataRow dwData)
        { 

            or1 = OracleDB.CreateCommand();  
            or1.CommandText = "SELECT DISTINCT VVLIEFSCH, S.* FROM VVLIEF V JOIN  VERSART  S ON VVERSART=VVVERSART AND VSPED=VVSPED JOIN VVTLIEF T ON V.VVMANDANT=VVTMANDANT AND VVLIEFSCH=VVLIEFSCH "
                       + " WHERE VVMANDANT=:VVMANDANT: AND  VVLIEFSCH=:VVLIEFSCH:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);  
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVER_1(DataRow dwData)
        { 

            or1 = OracleDB.CreateCommand();  
            or1.CommandText = "SELECT VVERSART,VSPED,VBEZ,VBEZ AS KEY FROM VERSART WHERE VVERSART LIKE '%" + dwData["VVERSART"].ToString().Trim() + "%'";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVER_2(DataRow dwData)
        {


            or1 = OracleDB.CreateCommand(); 
            or1.CommandText = "SELECT * FROM VERSART "
                +" WHERE VVERSART LIKE '%" + dwData["VVERSART"].ToString().Trim() + "%' AND VBEZ LIKE '%" + dwData["VBEZ"].ToString().Trim() + "%'";

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public void DeleteVERSART(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM VERSART  WHERE VMANDANT=:VMANDANT: AND VVERSART=:VVERSART: AND VSPED=:VSPED:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }

        public void UpdateVERSART(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                default:
                case 1:
                    or1.CommandText = "UPDATE VERSART SET VMANDANT=:VMANDANT:,VVERSART=:VVERSART:,VSPED=:VSPED:,VBEZ=:VBEZ:,VVSKLAS=:VVSKLAS:,VLIEFNR=:VLIEFNR:,VEBUCHNR=:VEBUCHNR:,VTPMLKZ=:VTPMLKZ:,VVEZWEIG=:VVEZWEIG:,VAZOLLST=:VAZOLLST:,VDISP=:VDISP:,VTEL=:VTEL:,VFRBRIEF=:VFRBRIEF:,VFRBUGRP=:VFRBUGRP:,VFRBPARAM=:VFRBPARAM:,VFRBANZ=:VFRBANZ:,VFRBTXT=:VFRBTXT:,VLADELST=:VLADELST:,VLDLUGRP=:VLDLUGRP:,VLDLPARAM=:VLDLPARAM:,VLDLANZ=:VLDLANZ:,VLDLTXT=:VLDLTXT:,VETIKETT=:VETIKETT:,VFORMETI=:VFORMETI:,VETIANZ=:VETIANZ:,VETIDRKZ=:VETIDRKZ:,VETITXT=:VETITXT:,VPRAEFIX=:VPRAEFIX:,VLFDNRANZ=:VLFDNRANZ:,VPRUEFVERF=:VPRUEFVERF:,VPRFELDLG1=:VPRFELDLG1:,VPRFELDKZ1=:VPRFELDKZ1:,VPRFELDLG2=:VPRFELDLG2:,VPRFELDKZ2=:VPRFELDKZ2:,VPRFELDLG3=:VPRFELDLG3:,VPRFELDKZ3=:VPRFELDKZ3:,VPRFELDLG4=:VPRFELDLG4:,VPRFELDKZ4=:VPRFELDKZ4:,VPRFELDLG5=:VPRFELDLG5:,VPRFELDKZ5=:VPRFELDKZ5:,VLO=:VLO:,VLB=:VLB:,VPLATZ=:VPLATZ:,VTTAG1=:VTTAG1:,VTTAG2=:VTTAG2:,VTTAG3=:VTTAG3:,VTTAG4=:VTTAG4:,VTTAG5=:VTTAG5:,VTTAG6=:VTTAG6:,VTTAG7=:VTTAG7:,VSTKGKZ=:VSTKGKZ:,VVERSD=:VVERSD:,VAVISBILD=:VAVISBILD:,VVSFREMDF=:VVSFREMDF:,VVSFREMDUF=:VVSFREMDUF:,VDRUCKLS=:VDRUCKLS:,VFRANKAT=:VFRANKAT:,VPACKTYP=:VPACKTYP:,VDEPOT=:VDEPOT:,VSERVICE=:VSERVICE:,VBOOKNO=:VBOOKNO:,VBOOKPR=:VBOOKPR:,VBOOKANZ=:VBOOKANZ:,VBOOKVERF=:VBOOKVERF:,VBOOKEB=:VBOOKEB:,VSUFFIX=:VSUFFIX:,VBOOKSUFF=:VBOOKSUFF:,VPPZVON=:VPPZVON:,VPPZBIS=:VPPZBIS:,VBPZVON=:VBPZVON:,VBPZBIS=:VBPZBIS:,VRVERSART=:VRVERSART:,VRSPED=:VRSPED:,VROUTING=:VROUTING:,VURL=:VURL:,VFILLER=:VFILLER:,VAENZL=:VAENZL:,VAENB=:VAENB:,VAEND=:VAEND:,VNEUB=:VNEUB:,VNEUD=:VNEUD:"
                        + " WHERE VMANDANT=:VMANDANT: AND VVERSART=:VVERSART: AND VSPED=:VSPED:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
